<?php

/**
 * This is the model class for table "category".
 *
 * The followings are the available columns in table 'category':
 * @property integer $identification
 * @property string $name
 * @property string $description
 * @property string $image
 * @property integer $parent
 * @property string $url_mapping
 * @property string $note
 * @property string $insert
 * @property string $update
 * @property string $delete
 */
class Category extends CActiveRecord
{
    //init contances
    const DEFAULT_SHEET_NAME = 'Category';

    //init properties
    public $key;
    public $parent_name;
    public $child_count;

    /**
     * Returns the static model of the specified AR class.
     *
     * @param string $className
     *            active record class name.
     * @return Category the static model class
     */
    public static function model ($className = __CLASS__)
    {
        return parent::model($className);
    }

    /**
     *
     * @return string the associated database table name
     */
    public function tableName ()
    {
        return 'category';
    }

    /**
     *
     * @return array validation rules for model attributes.
     */
    public function rules ()
    {
        // NOTE: you should only define rules for those attributes that
        // will receive user inputs.
        return array(
            array(
                'parent',
                'numerical',
                'integerOnly' => true
            ),
            array(
                'name',
                'length',
                'max' => 140
            ),
            array(
                'image, url_mapping',
                'length',
                'max' => 256
            ),
            array(
                'description, note, insert, update, delete',
                'safe'
            ),
            
            // The following rule is used by search().
            // Please remove those attributes that should not be searched.
            array(
                'identification, name, description, image, parent, url_mapping, note, insert, update, delete',
                'safe',
                'on' => 'search'
            )
        );
    }


    /**
     *
     * @return array relational rules.
     */
    public function relations ()
    {
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array();
    }

    /**
     *
     * @return array customized attribute labels (name=>label)
     */
    public function attributeLabels ()
    {
        return array(
            'identification' => 'key',
            'name'           => 'Name',
            'description'    => 'Description',
            'image'          => 'Image',
            'parent'         => 'Parent',
            'url_mapping'    => 'Url Mapping',
            'note'           => 'Note',
            'insert'         => 'Insert',
            'update'         => 'Update',
            'delete'         => 'Delete'
        );
    }

    /**
     * Retrieves a list of models based on the current search/filter conditions.
     *
     * @return CActiveDataProvider the data provider that can return the models
     *         based on the search/filter conditions.
     */
    public function search ()
    {
        // Warning: Please modify the following code to remove attributes that
        // should not be searched.
        $criteria = new CDbCriteria();

        $criteria->compare('identification', $this->identification);
        $criteria->compare('name', $this->name, true);
        $criteria->compare('description', $this->description, true);
        $criteria->compare('image', $this->image, true);
        $criteria->compare('parent', $this->parent);
        $criteria->compare('url_mapping', $this->url_mapping, true);
        $criteria->compare('note', $this->note, true);
        $criteria->compare('insert', $this->insert, true);
        $criteria->compare('update', $this->update, true);
        $criteria->compare('delete', $this->delete, true);

        return new CActiveDataProvider($this, array('criteria' => $criteria));
    }


    /**
     * get list of available definitions of column in excel from user
     *
     * @author  TuanNguyen
     * @version Create 2014-12-31
     * @param
     *
     * @return array
     */
    public static function getDefinitionExcelColumnList ()
    {
        return array (
            'name'        => array('ten', 'tendanhmuc', 'name', 'categoryname'),
            'description' => array('mota', 'desc', 'description'),
            'image'       => array('bieutuong', 'hinhanh', 'image', 'avatar'),
            'parent_name' => array('danhmuccha', 'root', 'parent'),
            'url_mapping' => array('duongdanao', 'urlmapping'),
            'note'        => array('ghichu', 'note')
        );
    }


    /**
     * get list of category from database
     *
     * @author  TuanNguyen
     * @version Created 2013-08-15
     * @param   int    $i_page
     *                 current page
     * @param   int    $i_amountPerPage
     *                 amount of data in a page
     * @param   string $s_order
     *                 SQL string to order data
     * @param   string $s_where
     *                 SQL string to filter data
     * @return  array
     */
    public static function getDataList ($i_page, $i_amountPerPage, $s_order, $s_where)
    {
        //Call SQL procedure
        $command = Yii::app()->db->createCommand('CALL category_01_getList(:p_page,:p_amount,:p_oder,:p_where)');

        //Set In parameter into SQL statement
        $command->bindValue('p_page',   $i_page);
        $command->bindValue('p_amount', $i_amountPerPage);
        $command->bindValue('p_oder',   $s_order);
        $command->bindValue('p_where',  $s_where);

        $arr_model = array();
        $arr_data  = $command->queryAll();

        foreach ($arr_data as $key => $row) {
            $m_model = new Category();
            $m_model->unsetAttributes();

            $m_model->identification = $row['identification'];
            $m_model->key            = $row['identification'];
            $m_model->name           = $row['name'];
            $m_model->description    = $row['description'];
            $m_model->image          = $row['image'];
            $m_model->parent         = $row['parent'];
            $m_model->parent_name    = $row['parent_name'];
            $m_model->url_mapping    = $row['url_mapping'];
            $m_model->note           = $row['note'];
            $m_model->insert         = $row['insert'];
            $m_model->update         = $row['update'];
            $m_model->delete         = $row['delete'];

            array_push($arr_model, $m_model);
        } // End foreach
        
        return $arr_model;
    } // End getDataList


    /**
     * get category by key from database
     *
     * @author  TuanNguyen
     * @version Created 2015-02-01
     * @param   int    $i_key
     *                 key of request category
     * @return  array or ErrorEntity
     */
    public static function getDataByKey ($i_key)
    {
        //Call SQL procedure
        $command = Yii::app()->db->createCommand('CALL category_02_getID(:p_key)');

        //Set In parameter into SQL statement
        $command->bindValue('p_key', $i_key, PDO::PARAM_INT);

        //execute SQL statement
        try {
            $row = $command->queryRow();
        } catch (CDbException $ex) {
            //SQL error
            $o_error          = new ErrorEntity();
            $o_error->errCode = 'ERR_2';
            $o_error->param   = array('PARAM_1' => $ex->getMessage());

            return $o_error;
        }

        if ( !$row ) {
            $o_error          = new ErrorEntity();
            $o_error->errCode = 'ERR_1';
            return $o_error;
        }

        $m_model = new Category();
        $m_model->unsetAttributes();

        $m_model->identification = $row['identification'];
        $m_model->key            = $row['identification'];
        $m_model->name           = $row['name'];
        $m_model->description    = $row['description'];
        $m_model->image          = $row['image'];
        $m_model->parent         = $row['parent'];
        $m_model->parent_name    = $row['parent_name'];
        $m_model->url_mapping    = $row['url_mapping'];
        $m_model->note           = $row['note'];
        $m_model->insert         = $row['insert'];
        $m_model->update         = $row['update'];
        $m_model->delete         = $row['delete'];

        return $m_model;
    } // End getDataList


    /**
     * save category entity into database
     *
     * @author  TuanNguyen
     * @version Created 2015-01-12
     * @param   Category $o_category
     *                   Category entity
     * @return  Category or ErrorEntity
     */
    public static function addDataToDb(Category $o_category)
    {
        $command = Yii::app()->db->createCommand('CALL category_03_insert (
                :p_name,
                :p_description,
                :p_image,
                :p_parent,
                :p_url
        )');

        $command->bindValue('p_name',        $o_category->name);
        $command->bindValue('p_description', CHtml::encode($o_category->description));
        $command->bindValue('p_image',       $o_category->image);
        $command->bindValue('p_parent',      $o_category->parent, PDO::PARAM_INT);
        $command->bindValue('p_url',         $o_category->url_mapping);

        try {
            $row = $command->queryRow();
        } catch (CDbException $ex) {
            $o_error          = new ErrorEntity();
            $o_error->errCode = 'ERR_2';
            $o_error->param   = array('PARAM_1' => $ex->getMessage());
            return $o_error;
        }//End catch

        if ( $row == false ) {
            $o_error          = new ErrorEntity();
            $o_error->errCode = 'ERR_1';
            $o_error->param   = array();
            return $o_error;
        }

        $m_model = clone(new Category());
        $m_model->unsetAttributes();

        $m_model->identification = $row['identification'];
        $m_model->key            = $row['identification'];
        $m_model->name           = $row['name'];
        $m_model->description    = $row['description'];
        $m_model->image          = $row['image'];
        $m_model->parent         = $row['parent'];
        $m_model->parent_name    = $row['parent_name'];
        $m_model->url_mapping    = $row['url_mapping'];
        $m_model->note           = $row['note'];
        $m_model->insert         = $row['insert'];
        $m_model->update         = $row['update'];
        $m_model->delete         = $row['delete'];
        
        return $m_model;
    }//End addDataToDb


    /**
     * update category entity into database
     *
     * @author  TuanNguyen
     * @version Created 2015-01-12
     * @param   Category $o_category
     *                   Category entity
     * @return  Category or ErrorEntity
     */
    public static function updateDataToDb(Category $o_category)
    {
        $command = Yii::app()->db->createCommand('CALL category_04_update
            (
                :p_key,
                :p_name,
                :p_description,
                :p_image,
                :p_parent,
                :p_url
        )');

        $command->bindValue('p_key',         $o_category->key,PDO::PARAM_INT);
        $command->bindValue('p_name',        $o_category->name);
        $command->bindValue('p_description', CHtml::encode($o_category->description));
        $command->bindValue('p_image',       $o_category->image);
        $command->bindValue('p_parent',      $o_category->parent, PDO::PARAM_INT);
        $command->bindValue('p_url',         $o_category->url_mapping);

        try {
            $row = $command->queryRow();
        } catch (CDbException $ex) {
            $o_error          = new ErrorEntity();
            $o_error->errCode = 'ERR_2';
            $o_error->param   = array('PARAM_1' => $ex->getMessage());
            return $o_error;
        }

        //If row is NULL, update is false
        if($row == false) {
            $o_error          = new ErrorEntity();
            $o_error->errCode = 'ERR_1';
            $o_error->param   = array();
            return $o_error;
        }//End if

        $m_model = new Category();
        $m_model->unsetAttributes();

        $m_model->identification = $row['identification'];
        $m_model->key            = $row['identification'];
        $m_model->name           = $row['name'];
        $m_model->description    = $row['description'];
        $m_model->image          = $row['image'];
        $m_model->parent         = $row['parent'];
        $m_model->parent_name    = $row['parent_name'];
        $m_model->url_mapping    = $row['url_mapping'];
        $m_model->note           = $row['note'];
        $m_model->insert         = $row['insert'];
        $m_model->update         = $row['update'];
        $m_model->delete         = $row['delete'];

        return $m_model;
    }//End updateDataToDb


    /**
     * Check category keys existing in database
     *
     * @author  TuanNguyen
     * @version Created 2015-02-10
     * @param   string $s_keys
     *                 list of category keys to check
     * @return  array or ErrorEntity
     */
    public static function isExistingCategories ($s_keys)
    {
        //Call SQL procedure
        $command = Yii::app()->db->createCommand('CALL category_06_countExistingCatgory(:p_keys)');

        //Set In parameter into SQL statement
        $command->bindValue('p_keys', $s_keys);

        //execute SQL statement
        try {
            $row = $command->queryRow();
        } catch (CDbException $ex) {
            //SQL error
            $o_error          = new ErrorEntity();
            $o_error->errCode = 'ERR_2';
            $o_error->param   = array('PARAM_1' => $ex->getMessage());

            return $o_error;
        }

        //No existing categories are found
        if ( !$row || $row['row_count'] < 1 ) {
            return false;
        }

        return true;
    }//End isExistingCategories


    /**
     * update category entity into database
     *
     * @author  TuanNguyen
     * @version Created 2015-01-12
     * @param   Category $o_category
     *                   Category entity
     * @return  Category or ErrorEntity
     */
    public static function deleteDataToDb($s_where)
    {
        //Call SQL procedure
        $command = Yii::app()->db->createCommand("CALL category_05_delete (:in_where)");  

        //Set In parameter into SQL statement
        $command->bindValue('in_where',$s_where);

        //execute SQL statement
        try {
            $i_result =  $command->execute();
        } catch (CDbException $ex) {
            //SQL error
            $o_error          = new ErrorEntity();
            $o_error->errCode = 'ERR_2';
            $o_error->param   = array('PARAM_1' => $ex->getMessage());

            return $o_error;
        }

        return true;
    }//End deleteDataToDb


}//End class